package com.querie.parser;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.StringTokenizer;

import com.querie.dao.DAOFactory;
import com.querie.dao.FragmentDAO;
import com.querie.dao.QueryDAO;
import com.querie.dao.QuerylogDAO;
import com.querie.model.beans.Fragment;
import com.querie.model.beans.Query;
import com.querie.model.beans.QueryLog;
import com.querie.util.SQLKeyword;

public class QueryParser {

	static QuerylogDAO qLDAO = DAOFactory.getQuerylogDAO();
	static FragmentDAO fragDAO = DAOFactory.getFragmentDAO();
	static QueryDAO queryDAO = DAOFactory.getQueryDAO();
//	static private BufferedWriter ignoretoken;
//	static private BufferedWriter acceptedToken;

	/**
	 * @param args
	 */
	public static void parse() {

		// Work on individual queries one by one.
		// Typical Query

		// Select XXX
		// from XXXX
		// where XXXX>XXXX, XXX!=XXXX, XXX IN ,
		// group by xxxx
		// having XXXX>XXXX, XXX!=XXXX, XXX IN ,
		// order by xxdhiasdf

		int pageOffSet = 0;
		int countOfRecords = 0;
		int index = 0;
		int numOfDash= 0;
		Query query = new Query();
		String selectFrag = new String();
		String fromFrag = new String();
		String whereFrag = new String();
		String groupByFrag = new String();
		String havingFrag = new String();
		String orderByFrag = new String();

		
		try {
//			ignoretoken = new BufferedWriter(new OutputStreamWriter(
//					new FileOutputStream("resources/ignoretoken.txt")));
//			acceptedToken = new BufferedWriter(new OutputStreamWriter(
//					new FileOutputStream("resources/acceptedtoken.txt")));
			countOfRecords = qLDAO.countAll();
			
			queryDAO.deleteAll();
			fragDAO.deleteAll();
			fragDAO.alterTable();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (countOfRecords % 20 != 0)
		{
			int mod = countOfRecords % 20;
			
			countOfRecords = countOfRecords - mod + 20;
		}
		List<QueryLog> listOfQueryLog = null;
		try {
			listOfQueryLog = qLDAO.queryAllWithPage(pageOffSet);
			System.out.println("Parsing the query log.......");
			while (listOfQueryLog != null && pageOffSet <= countOfRecords) {
				numOfDash = (int)((1.0*index/countOfRecords) * 100);
				System.out.print(""+numOfDash+"%   ");
				for (int index2 = 0; index2 < numOfDash; index2++)
				{
					System.out.print("#");
				}
				
				for (QueryLog qLog : listOfQueryLog) {
					index++;
					// // System.out//.println(qLog);

					String sql = qLog.getQuery();
					sql = sql.toLowerCase();
					sql = SQLKeyword.insertSpacesForOperators(sql);
					// // System.out//.println(sql);
					// // System.out//.println("getSelectFragment:"
//							+ getSelectFragment(sql));
					// // System.out//.println("getFromFragment:"
//							+ getFromFragment(sql));
					// // System.out//.println("getWhereFragment:"
//							+ getWhereFragment(sql));
					// // System.out//.println("getGroupByFragment:"
//							+ getGroupByFragment(sql));
					// // System.out//.println("getHavingFragment:"
//							+ getHavingFragment(sql));
					// // System.out//.println("getOrderByFragment:"
//							+ getOrderByFragment(sql));

					// System.out
							//.println("******************* Select Fragments ******************* ");
					selectFrag = processSelectFragment(getSelectFragment(sql));
					// // System.out//.println(selectFrag);
					// System.out
							//.println("******************* End Select Fragments ******************* ");

					// System.out
							//.println("******************* From Fragments ******************* ");
					fromFrag = processFromFragment(getFromFragment(sql));
					// // System.out//.println(fromFrag);

					// System.out
							//.println("******************* End From Fragments ******************* ");

					// System.out
							//.println("******************* Where Fragments ******************* ");
					whereFrag = processWhereFragment(getWhereFragment(sql));
					// // System.out//.println(whereFrag);
					// System.out
							//.println("******************* End Where Fragments ******************* ");

					// System.out
							//.println("******************* Group By Fragments ******************* ");
					groupByFrag = processGroupByFragment(getGroupByFragment(sql));
					// // System.out//.println(groupByFrag);
					// System.out
							//.println("******************* End Having Fragments ******************* ");

					// System.out
							//.println("******************* Having Fragments ******************* ");
					havingFrag = processHavingFragment(getHavingFragment(sql));
					// // System.out//.println(havingFrag);
					// System.out
							//.println("******************* End Having Fragments ******************* ");

					// System.out
							//.println("******************* Order By Fragments ******************* ");
					orderByFrag = processOrderByFragment(getOrderByFragment(sql));
					// // System.out//.println(orderByFrag);

					// System.out
							//.println("******************* End Order By Fragments ******************* ");
					query.setColumnfragid(selectFrag);
					query.setGroupfragid(groupByFrag);
					query.setHavingfragid(havingFrag);
					query.setOrderbyfragid(orderByFrag);
					query.setSessionid(qLog.getSessionId());
					query.setQueryid(qLog.getQueryId());
					query.setTablefragid(fromFrag);
					query.setWherefragid(whereFrag);

					queryDAO.insert(query);
//					new ConsoleReader().clearScreen();
				
				}
				pageOffSet += 20;
				listOfQueryLog = qLDAO.queryAllWithPage(pageOffSet);
				System.out.print("\r");
//				System.out.flush();
			}
//			ignoretoken.flush();
//			ignoretoken.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static String getSelectFragment(String sql) {

		// // System.out//.println("getSelectFragment called");
		String result = "";
		int fromIndex = 0;
		int toIndex = 0;
		try {
			fromIndex = sql.indexOf("select");

			if (fromIndex == -1)
				return result;
			fromIndex += 6;
			toIndex = sql.indexOf("from");

			if (fromIndex > 0 && toIndex > 0) {
				result = sql.substring(fromIndex, toIndex).trim();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getSelectFragment end with result: " + result);
		return result;
	}

	public static String getFromFragment(String sql) {
		// // System.out//.println("getFromFragment called");
		String result = "";
		int fromIndex = -1;
		int toIndex = -1;
		try {
			fromIndex = sql.indexOf("from");

			if (fromIndex == -1)
				return result;
			fromIndex += 4;
			toIndex = sql.indexOf("where");
			if (toIndex < 0) {
				toIndex = sql.indexOf("group by");
				if (toIndex < 0) {
					toIndex = sql.indexOf("order by");

					if (toIndex < 0)
						toIndex = sql.length();
				}
			}

			result = sql.substring(fromIndex, toIndex).trim();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getFromFragment end with result: " + result);
		return result;
	}

	public static String getWhereFragment(String sql) {

		// // System.out//.println("getWhereFragment called");
		String result = "";
		int fromIndex = -1;
		int toIndex = -1;
		try {
			fromIndex = sql.indexOf("where");

			if (fromIndex == -1)
				return result;

			fromIndex += 5;
			toIndex = sql.indexOf("group by");
			if (toIndex < 0) {
				toIndex = sql.indexOf("order by");
				if (toIndex < 0)
					toIndex = sql.length();
			}

			result = sql.substring(fromIndex, toIndex).trim();

		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getWhereFragment end with result: " + result);
		return result;
	}

	public static String getGroupByFragment(String sql) {
		// // System.out//.println("getGroupByFragment called");
		String result = "";
		int fromIndex = -1;
		int toIndex = -1;
		try {
			fromIndex = sql.indexOf("group by");

			if (fromIndex == -1)
				return result;
			fromIndex += 8;
			toIndex = sql.indexOf("having");
			if (toIndex < 0) {
				toIndex = sql.indexOf("order by");
				if (toIndex < 0)
					toIndex = sql.length();
				result = sql.substring(fromIndex, toIndex).trim();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getGroupByFragment end with result: " + result);
		return result;
	}

	public static String getHavingFragment(String sql) {
		// // System.out//.println("getHavingFragment called");
		String result = "";
		int fromIndex = -1;
		int toIndex = -1;
		try {
			fromIndex = sql.indexOf("having");

			if (fromIndex == -1)
				return result;
			fromIndex += 6;
			toIndex = sql.indexOf("order by");
			if (toIndex < 0)
				toIndex = sql.length();
			result = sql.substring(fromIndex, toIndex).trim();

		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getHavingFragment end with result: " + result);
		return result;
	}

	public static String getOrderByFragment(String sql) {
		// // System.out//.println("getOrderByFragment called");
		String result = "";
		int fromIndex = -1;
		int toIndex = -1;
		try {
			fromIndex = sql.indexOf("order by");

			if (fromIndex == -1)
				return result;
			fromIndex += 8;
			toIndex = sql.indexOf("asc");
			if (toIndex < 0) {
				toIndex = sql.indexOf("desc");
				if (toIndex < 0)
					toIndex = sql.length();
			}
			result = sql.substring(fromIndex, toIndex).trim();

		} catch (Exception e) {
			e.printStackTrace();
		}
		// // System.out//.println("getOrderByFragment end with result: " + result);
		return result;
	}

	public static String processSelectFragment(String selectFrag) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.
		Fragment foundFragment = new Fragment();

		StringTokenizer strToken = new StringTokenizer(selectFrag, " ");
		while (strToken.hasMoreTokens()) {
			String token = strToken.nextToken();
			
			if ( token.equalsIgnoreCase("*"))
			{
				long fragmentid = 0l;
				foundFragment.setFragment(token);
				foundFragment.setFragmentType("column");

				try {
					fragmentid = fragDAO.insert(foundFragment);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				if (!result.contains("" + fragmentid))
					result += (fragmentid + "::");
				
				continue;
			}
			String processedColumn = processTokensForSelect(token);
			if (!(processedColumn.equalsIgnoreCase("") || processedColumn
					.equalsIgnoreCase("*"))) {
				foundFragment.setFragment(processedColumn);
				foundFragment.setFragmentType("column");
				try {
					long fragmentid = 0l;
					fragmentid = fragDAO.insert(foundFragment);
					if (!result.contains("" + fragmentid))
						result += (fragmentid + "::");
				} catch (Exception e) {
					e.printStackTrace();
				}
				// add the processed colum string to result;
			} else {
//				try {
//					ignoretoken.write("Ignored token: " + token); ignoretoken.newLine(); ignoretoken.flush();
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//				// // System.out//.println("Ignored token: " + token);
			}
		}

		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}
		return result;
	}

	public static String processFromFragment(String fromFrag) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.

		StringTokenizer commaToken = new StringTokenizer(fromFrag, ",");
		Fragment foundFragment = new Fragment();

		while (commaToken.hasMoreTokens()) {
			StringTokenizer strToken = new StringTokenizer(
					commaToken.nextToken(), " ");
			foundFragment.setFragment(strToken.nextToken());
			foundFragment.setFragmentType("table");
			try {
				long fragmentid = fragDAO.insert(foundFragment);
				if (!result.contains("" + fragmentid))
					result += (fragmentid + "::");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}
		return result;
	}

	public static String processWhereFragment(String whereFrag) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.

		StringTokenizer strToken = new StringTokenizer(whereFrag, " ");

		while (strToken.hasMoreTokens()) {
			Fragment foundFragment = new Fragment();
			String token = strToken.nextToken();

			String processedColumn = processTokensForSelect(token);
			if (!processedColumn.equalsIgnoreCase("")) {
				// add the processed colum string to result;
				foundFragment.setFragment(processedColumn);
				foundFragment.setFragmentType("where");
				try {
					long fragmentid = fragDAO.insert(foundFragment);
					if (!result.contains("" + fragmentid))
						result += (fragmentid + "::");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} else {
//				try {
//					ignoretoken.write("Ignored token: " + token); ignoretoken.newLine(); ignoretoken.flush();
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//				// // System.out//.println("Ignored token: " + token);
			}

		}
		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}

		return result;
	}

	private static String processTokensForSelect(String input) {
		String result = "";

		// Try to include
		input = input.trim();

		if (SQLKeyword.isSQLKeyWord(input) || SQLKeyword.isNumeric(input)
				|| SQLKeyword.containsOperator(input)) {
			return result;
		}
		// else
		// if (input.contains("(") || input.contains(")")) {
		// result = SQLKeyword.fetchFragmentFromFunction(input);
		// }
		else {
			result = input;
		}

		if (result.contains(".")) {
			StringTokenizer strToken = new StringTokenizer(result, ".");
			if (strToken.hasMoreTokens())
				strToken.nextToken();
			if (strToken.hasMoreTokens()) {
				result = strToken.nextToken();
			}
		}

		if (result.endsWith("."))
		{
			result = "";
		}
		else if (result.startsWith("."))
		{
			result = result.substring(1);
		}
		// if (result.startsWith("[")) {
		// int indexOfOpenBracket = result.indexOf("[");
		// int indexOfLastBracket = result.lastIndexOf("]");
		//
		// if (indexOfOpenBracket > 0) {
		// if (indexOfLastBracket > 0) {
		// result = result.substring(indexOfOpenBracket + 1,
		// indexOfLastBracket);
		// }
		// else
		// {
		// result = "";
		// }
		// }
		// }

		// if (result.startsWith("'")) {
		// int indexOfOpenBracket = result.lastIndexOf("'");
		// int indexOfLastBracket = result.indexOf("'");
		//
		// if (indexOfOpenBracket > 0) {
		// if (indexOfLastBracket > 0)
		// {
		// result = result.substring(indexOfOpenBracket + 1,
		// indexOfLastBracket);
		// }
		// else
		// {
		// result = result.substring(indexOfOpenBracket + 1,
		// indexOfLastBracket);
		// }
		//
		// }
		// }
		if (SQLKeyword.isNumeric(result))
			result = "";
		return result;
	}

	public static String processGroupByFragment(String groupByFrag) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.

		Fragment foundFragment = new Fragment();

		StringTokenizer strToken = new StringTokenizer(groupByFrag, " ");

		while (strToken.hasMoreTokens()) {
			String token = strToken.nextToken();
			String processedColumn = processTokensForSelect(token);
			if (!processedColumn.equalsIgnoreCase("")) {
				foundFragment.setFragment(processedColumn);
				foundFragment.setFragmentType("group");
				try {
					long fragmentId = fragDAO.insert(foundFragment);
					if (!result.contains("" + fragmentId))
						result += (fragmentId + "::");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				// add the processed colum string to result;
			} else {
//				try {
//					ignoretoken.write("Ignored token: " + token); ignoretoken.newLine(); ignoretoken.flush();
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//				// // System.out//.println("Ignored token: " + token);
			}
		}

		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}

		return result;
	}

	public static String processHavingFragment(String having) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.

		Fragment foundFragment = new Fragment();

		StringTokenizer strToken = new StringTokenizer(having, " ");

		while (strToken.hasMoreTokens()) {
			String token = strToken.nextToken();
			String processedColumn = processTokensForSelect(token);
			if (!processedColumn.equalsIgnoreCase("")) {
				foundFragment.setFragment(processedColumn);
				foundFragment.setFragmentType("having");
				try {
					long fragmentId = fragDAO.insert(foundFragment);
					if (!result.contains("" + fragmentId))
						result += (fragmentId + "::");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				// add the processed colum string to result;
			} else {
//				try {
//					ignoretoken.write("Ignored token: " + token); ignoretoken.newLine(); ignoretoken.flush();
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//				// // System.out//.println("Ignored token: " + token);
			}
		}

		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}
		return result;
	}

	public static String processOrderByFragment(String orderBy) {
		String result = new String();

		// First tokenize the string based on spaces
		// Then ignore the sql keywords
		// some keyword are in format keyword(attribute).. need to process it
		// and extract the attribute from them.
		// Check and Ignore all the numeric values.

		Fragment foundFragment = new Fragment();

		StringTokenizer strToken = new StringTokenizer(orderBy, " ");

		while (strToken.hasMoreTokens()) {
			String token = strToken.nextToken();
			String processedColumn = processTokensForSelect(token);
			if (!processedColumn.equalsIgnoreCase("")) {
				// add the processed colum string to result;
				foundFragment.setFragment(processedColumn);
				foundFragment.setFragmentType("orderby");
				try {
					long fragmentid = fragDAO.insert(foundFragment);
					if (!result.contains("" + fragmentid))
						result += (fragmentid + "::");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			} else {
//				try {
//					ignoretoken.write("Ignored token: " + token); ignoretoken.newLine(); ignoretoken.flush();
//				} catch (IOException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//				// // System.out//.println("Ignored token: " + token);
			}
		}

		if (result.contains("::")) {
			result = result.substring(0, result.length() - 2);
		}

		return result;
	}

	public static <T> List<T> union(List<T> list1, List<T> list2) {
		Set<T> set = new HashSet<T>();

		set.addAll(list1);
		set.addAll(list2);

		return new ArrayList<T>(set);
	}

	public static void main(String a[]) {
		String selectFrag = new String();
		String fromFrag = new String();
		String whereFrag = new String();
		String groupByFrag = new String();
		String havingFrag = new String();
		String orderByFrag = new String();

		// String sql =
		// "select top 1000 str(s.ra,13,8) as ra,str(s.[dec],13,8) as dec,s.specobjid,s.z,"
		// +
		// "p.psfmag_u,p.psfmag_g,p.psfmag_r,p.psfmag_i,p.psfmag_z, 'u' as filter from specobj as s,  "
		// +
		// "photoobj as p where  s.z > 0.3 and (s.specclass = dbo.fspecclass('qso')) and s.z < 0.7 and "
		// +
		// "(p.psfmag_u - p.psfmag_g) > 0.7 and ( p.type = 3 or p.type = 6) and s.bestobjid = p.objid";
		// String sql =
		// "select top 1000  ra, dec from specobjall where ra between 159.6 and "
		// +
		// "160. and dec between -0.5 and 0.0 and z between 0.01 and 0.02";
		// String sql =
		// "select top 1000  distinct r.run run  from runqa r, phototag p where r.field=p.field and r.run=p.run and r.run in  (94,125,1033,1040,1056,1752,1755,1894,2385,2570,2578,2579,2583,2585,2589,2649,2650,2659,2662,2677,2700,2708,2709,2728,2738,2768,2820,2855,2856,2861,2873,2886,2960,2968,3322,3325,3355,3360,3362,3384,3388,3427,3430,3434,3437,3438,3460,3465,4128,4136,4153,4157,4158,4187,4188,4192,4198,4207,4247,4253,4263,4288,4797,4930,5042,5052,5658) and r.fieldqall in (1,2,3) and p.type=3";
		String sql = "select top 1000  ra, dec from specobjall where ra between 159.6 and 160. and dec between -0.5 and 0.0 and z between 0.01 and 0.02";
		sql = sql.toLowerCase();
		sql = SQLKeyword.insertSpacesForOperators(sql);
		// // System.out//.println(sql);
		// // System.out//.println("getSelectFragment:" + getSelectFragment(sql));
		// // System.out//.println("getFromFragment:" + getFromFragment(sql));
		// // System.out//.println("getWhereFragment:" + getWhereFragment(sql));
		// // System.out//.println("getGroupByFragment:" + getGroupByFragment(sql));
		// // System.out//.println("getHavingFragment:" + getHavingFragment(sql));
		// // System.out//.println("getOrderByFragment:" + getOrderByFragment(sql));

		// System.out
				//.println("******************* Select Fragments ******************* ");
		selectFrag = processSelectFragment(getSelectFragment(sql));
		// // System.out//.println(selectFrag);
		// System.out
				//.println("******************* End Select Fragments ******************* ");

		// System.out
				//.println("******************* From Fragments ******************* ");
		fromFrag = processFromFragment(getFromFragment(sql));
		// // System.out//.println(fromFrag);

		// System.out
				//.println("******************* End From Fragments ******************* ");

		// System.out
				//.println("******************* Where Fragments ******************* ");
		whereFrag = processWhereFragment(getWhereFragment(sql));
		// // System.out//.println(whereFrag);
		// System.out
				//.println("******************* End Where Fragments ******************* ");

		// System.out
				//.println("******************* Group By Fragments ******************* ");
		groupByFrag = processGroupByFragment(getGroupByFragment(sql));
		// // System.out//.println(groupByFrag);
		// System.out
				//.println("******************* End Having Fragments ******************* ");

		// System.out
				//.println("******************* Having Fragments ******************* ");
		havingFrag = processHavingFragment(getHavingFragment(sql));
		// // System.out//.println(havingFrag);
		// System.out
				//.println("******************* End Having Fragments ******************* ");

		// System.out
				//.println("******************* Order By Fragments ******************* ");
		orderByFrag = processOrderByFragment(getOrderByFragment(sql));
		// // System.out//.println(orderByFrag);

		// System.out
				//.println("******************* End Order By Fragments ******************* ");
	}

}
